﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'proc_cm_GetDailyWordStatus')
BEGIN
    PRINT 'Dropping Procedure proc_cm_GetDailyWordStatus'
    DROP  Procedure  proc_cm_GetDailyWordStatus
END
GO

PRINT 'Creating Procedure proc_cm_GetDailyWordStatus'
GO

CREATE PROCEDURE [dbo].[proc_cm_GetDailyWordStatus]
	@pDailyWordId [uniqueidentifier]
	, @pUserName [nvarchar](256)
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @vUserId uniqueidentifier

	SELECT @vUserId = UserId
	FROM [dbo].[aspnet_Users]
	WHERE [UserName] = @pUserName

	-- CREATE RECORD IF ONE IS NOT ALREADY THERE
	IF NOT EXISTS (SELECT TOP 1 NULL FROM [dbo].[tbl_dailyword_status] (NOLOCK) WHERE [dailyword_id] = @pDailyWordId AND [UserId] = @vUserId)
	BEGIN
		INSERT INTO [dbo].[tbl_dailyword_status]
		(
			[dailyword_status_id]
			,[dailyword_id]
			,[UserId]
			,[read_ind]
			,[reflection_txt]
			,[submitted_ind]
			,[created_dttm]
			,[submitted_dttm]
			,[score_num]
			,[perfect_score_num]
		)
		SELECT
			NEWID()
			, @pDailyWordId
			, @vUserId
			, 0
			, null
			, 0
			, GETDATE()
			, null
			, null
			, null
	END

	SELECT 
		[dailyword_status_id]
		,[dailyword_id]
		,[UserId]
		,[read_ind]
		,[reflection_txt]
		,[submitted_ind]
		,[created_dttm]
		,[submitted_dttm]
		,[score_num]
		,[perfect_score_num]
	FROM
		[dbo].[tbl_dailyword_status] (NOLOCK)
	WHERE
		[dailyword_id] = @pDailyWordId
		AND [UserId] = @vUserId

	SELECT 
		da.[dailyword_answer_id]
		,da.[dailyword_question_id]
		,da.[dailyword_status_id]
		,da.[answer_txt]
		,da.[correct_ind]
		,da.[created_dttm]
		,da.[updated_dttm]
	FROM 
		[dbo].[tbl_dailyword_answer] da (nolock)
		INNER JOIN [dbo].[tbl_dailyword_status] ds (nolock) ON da.dailyword_status_id = ds.dailyword_status_id
	WHERE
		ds.[dailyword_id] = @pDailyWordId
		AND ds.[UserId] = @vUserId
END
GO

GRANT EXEC ON dbo.proc_cm_GetDailyWordStatus TO PUBLIC
GO

